﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Com.LongER.Framework.Utils;
using System.Data.SqlClient;
using System.Data;

namespace Com.LongER.Business.ZAH001
{
    /// <summary>
    /// 
    /// </summary>
  public  class CZAH001:ABussinessBase ,IZAH001
  {
      /// <summary>
      /// 
      /// </summary>
      /// <param name="strUserid"></param>
      /// <param name="strPassword"></param>
      /// <returns></returns>
      public ZAH001DS.USER_TBLDataTable GetUserDataByUserid(String strUserid, String strPassword)
        {

            ////DB接続文字列取得
            var connectionString = ConnectionStringUtil.GetConnectionString();
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            string cnnstr = string.Empty;

            #region sqlContent
            // C#コード自動作成
            StringBuilder objSqlContent = new StringBuilder();
            objSqlContent.Append(" SELECT                                                                          ");
            objSqlContent.Append("     U.USERID                                                                    ");
            objSqlContent.Append("     ,U.USERID_HANZI                                                             ");
            objSqlContent.Append("     ,S.STAFF_ID                                                                 ");
            objSqlContent.Append("     ,S.STAFF_HANZI                                                              ");
            objSqlContent.Append("     ,B.CODE1_HANZI AS  BUMEN_HANZI                                              ");
            objSqlContent.Append("     ,Z.CODE1_HANZI AS  ZHIWEI_HANZI                                             ");
            objSqlContent.Append("     ,U.PWD                                                                      ");
            objSqlContent.Append(" FROM                                                                            ");
            objSqlContent.Append("     M_STAFF AS S                                                                ");
            objSqlContent.Append("     INNER JOIN                                                                  ");
            objSqlContent.Append("     M_USER AS U                                                                 ");
            objSqlContent.Append("     ON                                                                          ");
            objSqlContent.Append("         S.STAFF_ID = U.STAFF_ID                                                 ");
            objSqlContent.Append("     AND U.USERID = @USERID                                                      ");
            objSqlContent.Append("     AND U.PWD = @PWD                                                            ");
            objSqlContent.Append("     INNER JOIN                                                                  ");
            objSqlContent.Append("     M_TONGYONG AS B                                                             ");
            objSqlContent.Append("     ON                                                                          ");
            objSqlContent.Append("         S.BUMEN = B.KEY1                                                        ");
            objSqlContent.Append("     AND B.CODE_CD = 'BUMN'                                                      ");
            objSqlContent.Append("     INNER JOIN                                                                  ");
            objSqlContent.Append("     M_TONGYONG AS Z                                                             ");
            objSqlContent.Append("     ON                                                                          ");
            objSqlContent.Append("         S.ZHIWEI = Z.KEY1                                                       ");
            objSqlContent.Append("     AND Z.CODE_CD = 'ZHIW'                                                      ");

            #endregion
            cnnstr = objSqlContent.ToString();

            SqlCommand cmd = new SqlCommand(cnnstr, con);
            cmd.CommandText = cnnstr;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Parameters.Add(getSqlParameter("USERID", SqlDbType.Char, strUserid));
            cmd.Parameters.Add(getSqlParameter("PWD", SqlDbType.VarChar, strPassword));

            SqlDataReader odr = cmd.ExecuteReader();

            ZAH001DS.USER_TBLDataTable tb = new ZAH001DS.USER_TBLDataTable();
            if (odr.HasRows)
            {
                // データ有り
                while (odr.Read())
                {
                    ZAH001DS.USER_TBLRow dr = tb.NewUSER_TBLRow();
                    dr.USERID = odr["USERID"].ToString();
                    dr.USERID_HANZI = odr["USERID_HANZI"].ToString();
                    dr.STAFF_ID = odr["STAFF_ID"].ToString();
                    dr.STAFF_HANZI = odr["STAFF_HANZI"].ToString();
                    dr.BUMEN_HANZI = odr["BUMEN_HANZI"].ToString();
                    dr.ZHIWEI_HANZI = odr["ZHIWEI_HANZI"].ToString();
                    dr.PWD = odr["PWD"].ToString();

                    tb.AddUSER_TBLRow(dr);
                }
            }

            con.Close();
            return tb;
        }

    }
}
